package com.plian.system.service.sys.excel;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.google.gson.Gson;
import com.plian.Tools.DateUtil;
import com.plian.Tools.*;
import com.plian.Tools.pagedata.PageData;
import com.plian.system.bean.sys.SafeProdExcelBean;
import com.plian.system.cache.UserCache;
import com.plian.system.config.exception.MyRuntimeException;
import com.plian.system.constant.sys.AutoExcelConstant;
import com.plian.system.constant.sys.FormStatusConstant;
import com.plian.system.constant.yth.YthConstant;
import com.plian.system.dao.sys.excel.AutoExcelDao;
import com.plian.system.dao.sys.file.FileDao;
import com.plian.system.entity.sys.ApprovalMessage;
import com.plian.system.entity.sys.excel.ExcelBill;
import com.plian.system.mapper.sys.excel.AutoExcelMapper;
import com.plian.system.model.CommonCode;
import com.plian.system.model.ListResult;
import com.plian.system.model.ObjResult;
import com.plian.system.model.page.Page;
import com.plian.system.mybatis.base.BaseFlowService;
import com.plian.system.service.sys.file.FileService;
import com.plian.system.service.sys.message.IApprovalMessageService;
import com.plian.system.service.wf.FormWorkflowService;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.map.LinkedMap;
import org.apache.commons.lang.exception.ExceptionUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.flowable.engine.HistoryService;
import org.flowable.engine.RuntimeService;
import org.flowable.engine.history.HistoricProcessInstance;
import org.flowable.engine.runtime.ProcessInstance;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import static com.plian.system.constant.sys.FormTypeConstant.SYS_AUTO_EXCEL;


@Slf4j
@Service
@AllArgsConstructor
public class AutoExcelService implements BaseFlowService {
    private AutoExcelDao autoExcelDao;

    FileService fileService;

    private FormWorkflowService formWorkflowService;

    private RuntimeService runtimeService;

    private HistoryService historyService;

    private AutoExcelMapper autoExcelMapper;

    private IApprovalMessageService approvalMessageService;
    private FileDao fileDao;

    private static Pattern PATTERN = Pattern.compile(".0$");

    /*
     * 保存表格
     *
     * */
    public ObjResult saveAutoExcel(PageData pd) {
        ObjResult objResult;
        try {
            String startTime = (String)pd.get("startTime");
            String endTime = (String) pd.get("endTime");
            if (StringUtil.isNotBlank(startTime)){
                pd.put("startTime", startTime.substring(0, 10));
            }
            if (StringUtil.isNotBlank(endTime)){
                pd.put("endTime", endTime.substring(0, 10));
            }
            if (Tools.isEmpty(pd.getString("id"))) {
                pd.put("id", UuidUtil.get32UUID());
                pd.put("createTime", new Date());
                pd.put("isDel", "0");
                //保存附件
                fileService.saveAll(pd.getString("id"), (List<HashMap>) pd.get("list_doc"));
                autoExcelDao.saveAutoExcel(pd);
                if (Tools.notEmpty(pd.getString("tableName"))) {
                    String billName = "excel_bill_" + pd.getString("tableName");
                    String valueName = "excel_value_" + pd.getString("tableName");
                    autoExcelDao.createBill(billName);
                    autoExcelDao.createValue(valueName);
                }
            } else {
                pd.put("updateTime", new Date());
                autoExcelDao.editAutoExcel(pd);
                //保存附件
                fileService.edit(pd.getString("id"), (List<HashMap>) pd.get("list_doc"));
            }
            objResult = new ObjResult(CommonCode.SUCCESS);
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            objResult = new ObjResult(CommonCode.ERROR);
            return objResult;
        }
        return objResult;
    }
    public ObjResult saveAutoExcelFile(PageData pd){
        ObjResult objResult =null;
        List<HashMap> docList=(List<HashMap>)pd.get("docList");
        boolean flag = false;
        if (!Optional.ofNullable(pd.getString("id")).isPresent()) {
            flag = true;
        }
        if (flag){
            objResult=fileService.saveAll(String.valueOf(pd.getString("id")), docList);
        }else {
            objResult= fileService.edit(String.valueOf(pd.getString("id")), docList);
        }
        return objResult;
    }
    /*
     * 列表界面
     * */
    public ListResult<PageData> listAutoExcel(PageData pd) {
        try {
            Page page = new Page(true, pd);
            List<PageData> list = autoExcelDao.listAutoExcel(page);
            if (CollectionUtil.isNotEmpty(list)){
                for (PageData excelPd : list){
                    List<PageData> fileList = fileDao.findParentId(String.valueOf(excelPd.getString("id")));
                    if (CollectionUtil.isNotEmpty(fileList)){
                        for (PageData pageData : fileList){
                            fileService.setViewInfo(pageData);
                        }
                        excelPd.put("file", fileList);
                    }
                    Date startTime = (Date)excelPd.get("startTime");
                    Date endTime = (Date) excelPd.get("endTime");
                    if (startTime != null){
                        excelPd.put("startTime", DateUtil.format(startTime, DateUtil.PATTERN_DATE));
                    }
                    if (endTime != null){
                        excelPd.put("endTime", DateUtil.format(endTime, DateUtil.PATTERN_DATE));
                    }
                }
            }
            ListResult<PageData> Result = new ListResult<>(CommonCode.SUCCESS);
            Result.setTotal(list.size());
            Result.setList(list);
            return Result;
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            return new ListResult<>(CommonCode.ERROR);
        }
    }

    /*
     * 列表界面
     * */
    public ObjResult jsonToForm(PageData pd) {
        ObjResult objResult;
        try {
            PageData excel = autoExcelDao.findExcelById(pd.getString("id"));
            List<PageData> list_form = JSONArray.parseArray(excel.getString("jsonText"), PageData.class);
            pd.put("excelId", pd.getString("id"));
            List<PageData> exit_form = autoExcelDao.findByExcelId(pd);
            List<String> ids = new ArrayList<>();
            if (exit_form != null && exit_form.size() > 0) {
                for (PageData idpd : exit_form) {
                    ids.add(idpd.getString("formId"));
                }
                autoExcelDao.delForm(ids);
            }
            List<PageData> save_form = new ArrayList<>();
            List<PageData> save_col = new ArrayList<>();
            int i = 1;
            for (PageData jb : list_form) {
                PageData formpd = new PageData();
                formpd.put("formId", UuidUtil.get32UUID());
                formpd.put("name", jb.getString("title"));
                formpd.put("orderNumber", i);
                formpd.put("isMust", 0);
                formpd.put("isAdd", 0);
                formpd.put("isChange", 0);
                formpd.put("isOrder", 0);
                formpd.put("excelId", pd.getString("id"));
                save_form.add(formpd);
                List<String> list_col = JSONArray.parseArray(JSONArray.toJSONString(jb.get("colWidths")), String.class);

                for (int j = 1; j <= list_col.size(); j++) {
                    PageData colpd = new PageData();
                    colpd.put("columnId", UuidUtil.get32UUID());
                    colpd.put("orderNumber", j);
                    colpd.put("formId", formpd.getString("formId"));
                    colpd.put("isNumValue", 0);
                    save_col.add(colpd);

                }
                i++;
            }
            autoExcelDao.saveAllForm(save_form);
            autoExcelDao.saveAllColumn(save_col);
            objResult = new ObjResult(CommonCode.SUCCESS);

        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            objResult = new ObjResult(CommonCode.ERROR);
            return objResult;
        }
        return objResult;
    }

    /**
     * 删除
     *
     * @throws Exception
     */
    public ObjResult deleteAutoExcel(List<String> list) {
        ObjResult objResult;
        try {
            autoExcelDao.delAutoExcel(list);
            objResult = new ObjResult(CommonCode.SUCCESS);
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            objResult = new ObjResult(CommonCode.ERROR);
            return objResult;
        }
        return objResult;
    }

    /*
     * 保存表格
     *
     * */
    public ObjResult saveExcelForm(PageData pd) {
        ObjResult objResult;
        try {
            if (Tools.isEmpty(pd.getString("formId"))) {
                pd.put("formId", UuidUtil.get32UUID());
                autoExcelDao.saveForm(pd);
            } else {
                autoExcelDao.editForm(pd);
            }
            objResult = new ObjResult(CommonCode.SUCCESS);
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            objResult = new ObjResult(CommonCode.ERROR);
            return objResult;
        }
        return objResult;
    }

    /*
     * 列表界面
     * */
    public ListResult<PageData> listExcelForm(PageData pd) {
        try {
            Page page = new Page(true, pd);
            List<PageData> list = autoExcelDao.listForm(page);
            ListResult<PageData> Result = new ListResult<>(CommonCode.SUCCESS);
            Result.setTotal(page.getTotal());
            Result.setList(list);
            return Result;
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            return new ListResult<>(CommonCode.ERROR);
        }
    }

    /*
     * 列表界面
     * */
    public ListResult<PageData> selectedExcelForm(PageData pd) {
        try {
            PageData excel = autoExcelDao.findByCode(pd);
            pd.put("excelId", excel.getString("id"));
            List<PageData> list = autoExcelDao.findByExcelId(pd);
            ListResult<PageData> Result = new ListResult<>(CommonCode.SUCCESS);
            Result.setList(list);
            return Result;
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            return new ListResult<>(CommonCode.ERROR);
        }
    }

    /**
     * 删除
     *
     * @throws Exception
     */
    public ObjResult deleteForm(List<String> list) {
        ObjResult objResult;
        try {
            autoExcelDao.delForm(list);
            objResult = new ObjResult(CommonCode.SUCCESS);
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            objResult = new ObjResult(CommonCode.ERROR);
            return objResult;
        }
        return objResult;
    }

    /*
     * 保存column
     *
     * */
    public ObjResult saveExcelColumn(PageData pd) {
        ObjResult objResult;
        try {
            if (Tools.isEmpty(pd.getString("columnId"))) {
                pd.put("columnId", UuidUtil.get32UUID());
                autoExcelDao.saveColumn(pd);
            } else {
                autoExcelDao.editColumn(pd);
            }
            objResult = new ObjResult(CommonCode.SUCCESS);
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            objResult = new ObjResult(CommonCode.ERROR);
            return objResult;
        }
        return objResult;
    }

    /*
     * 列表界面
     * */
    public ListResult<PageData> listExcelColumn(PageData pd) {
        try {
            Page page = new Page(true, pd);
            List<PageData> list = autoExcelDao.listColumn(page);
            ListResult<PageData> Result = new ListResult<>(CommonCode.SUCCESS);
            Result.setTotal(page.getTotal());
            Result.setList(list);
            return Result;
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            return new ListResult<>(CommonCode.ERROR);
        }
    }

    /**
     * 删除
     *
     * @throws Exception
     */
    public ObjResult deleteColumn(List<String> list) {
        ObjResult objResult;
        try {
            autoExcelDao.delColumn(list);
            objResult = new ObjResult(CommonCode.SUCCESS);
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            objResult = new ObjResult(CommonCode.ERROR);
            return objResult;
        }
        return objResult;
    }
    /*
     * 保存主表
     *
     * */

    public ObjResult saveExcelBill(PageData pd) {
        ObjResult objResult;
        try {
            PageData excel = autoExcelDao.findByCode(pd);
            pd.put("orgId", TokenUtil.getTokenOrgId());
            pd.put("tableName", "excel_bill_" + excel.getString("tableName"));
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            if (Tools.isEmpty(pd.getString("reportTime"))) {
                pd.put("reportTime", df.format(new Date()));
            } else {
                switch (excel.getInteger("fillType")) {
                    case 0:
                        pd.put("reportTime", pd.getString("reportTime") + "-01");
                        break;
                    case 1:
                        if ("1".equals(pd.getString("season"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-01-01");
                        } else if ("2".equals(pd.getString("season"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-04-01");
                        } else if ("3".equals(pd.getString("season"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-07-01");
                        } else if ("4".equals(pd.getString("season"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-10-01");
                        } else {
                            objResult = new ObjResult(CommonCode.FAIL);
                            objResult.setMessage("季度填报错误");
                        }
                        break;
                    case 2:
                        pd.put("reportTime", pd.getString("reportTime") + "-01-01");
                        break;
                    case 3:
                        pd.put("reportTime", pd.getString("reportTime"));
                        break;
                    case 4:
                        //填报范围 上半年 全年
                        if ("1".equals(pd.getString("isYearly"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-06-01");
                        }else if("2".equals(pd.getString("isYearly"))){
                            pd.put("reportTime", pd.getString("reportTime") + "-12-01");
                        }else {
                            objResult = new ObjResult(CommonCode.FAIL);
                            objResult.setMessage("填报范围错误");
                        }
                        break;
                    case 5:
                        if ("1".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-01-01");
                        }else if ("2".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-02-01");
                        }else if ("3".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-03-01");
                        }else if ("4".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-04-01");
                        }else if ("5".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-05-01");
                        }else if ("6".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-06-01");
                        }else if ("7".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-07-01");
                        }else if ("8".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-08-01");
                        }else if ("9".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-09-01");
                        }else if ("10".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-10-01");
                        }else if ("11".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-11-01");
                        }else if ("12".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-12-01");
                        } else {
                            objResult = new ObjResult(CommonCode.FAIL);
                            objResult.setMessage("月度填报错误");
                        }
                        break;
                    default:
                        break;
                }
            }
            PageData l = autoExcelDao.findByDate(pd);
            // 安全生产隐患排查表放开日期限制
            if (l == null || "0004".equals(pd.getString("code"))) {
                pd.put("id", UuidUtil.get32UUID());
                pd.put("status", "0");
                pd.put("formType", SYS_AUTO_EXCEL);
                pd.put("applicant", TokenUtil.getTokenUserId());
                pd.put("excelId", excel.getString("id"));
                autoExcelDao.saveBill(pd);
                objResult = new ObjResult(CommonCode.SUCCESS);
            } else {
                objResult = new ObjResult(CommonCode.EXIST);
                objResult.setMessage("已存在当前日期的报表");
                return objResult;
            }
            HashMap<String, Object> processVariable = new HashMap<>();
            processVariable.put("code", pd.getString("code"));
            formWorkflowService.startProcessAndSimulate(pd.getString("id"), pd.getString("formType"), processVariable);
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            objResult = new ObjResult(CommonCode.ERROR);
            return objResult;
        }

        return objResult;
    }

    /**
     * 保存
     *
     * @param
     * @throws Exception
     */
    public ObjResult saveValue(PageData pd) {
        ObjResult objResult;
        try {
            PageData excel = autoExcelDao.findExcelById(autoExcelDao.findFormById(pd).getString("excelId"));
            //            保存关联表信息
            PageData pd_link = new PageData();
            List<PageData> old_link = autoExcelDao.findLinksById(pd);
            if (old_link != null) {
                autoExcelDao.dellink(pd);
            }
            pd_link.put("linkId", UuidUtil.get32UUID());
            pd_link.put("formId", pd.getString("formId"));
            pd_link.put("id", pd.getString("id"));
            if (Tools.notEmpty(pd.getString("staging"))) {
                pd_link.put("status", "-1");
            } else {
                pd_link.put("status", "0");
            }
            autoExcelDao.savelink(pd_link);
            List<PageData> l1 = new ArrayList<>();
            for (Object s : pd.keySet()) {
                int i = 1;
                /*取key值为co_的value值*/
                if ("orgId".equals(s) || "year".equals(s) || "id".equals(s) || "orgId".equals(s) || "status".equals(s) || "formId".equals(s) || "staging".equals(s)) {
                    continue;
                } else {
                    List<List<Object>> l = (List<List<Object>>) pd.get(s);
                    for (List<Object> p : l) {
                        PageData p1 = new PageData();
                        p1.put("columnId", s);
                        p1.put("orderNumber", i);
                        p1.put("values", p.get(0));
                        p1.put("valueId", UuidUtil.get32UUID());
                        p1.put("linkId", pd_link.getString("linkId"));
                        Object obj = p.get(1);
                        if (obj instanceof LinkedMap){
                            p1.put("realValues", p.get(0));
                        }
                        l1.add(p1);
                        i++;
                    }
                }
            }
            PageData pd_value = new PageData();
            pd_value.put("list", l1);
            pd_value.put("tableName", "excel_value_" + excel.getString("tableName"));
            autoExcelDao.savevalue(pd_value);
            objResult = new ObjResult(CommonCode.SUCCESS);
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            objResult = new ObjResult(CommonCode.ERROR);
            return objResult;
        }
        return objResult;
    }

    /*
     * 列表界面
     * */
    public ListResult<PageData> listpage(PageData pd) {
        try {
            if (Tools.notEmpty(pd.getString("orgIds"))) {
                List<String> orgIds = (List<String>) pd.get("orgIds");
                pd.put("orgIds", orgIds);
            } else {
                if (Tools.notEmpty(pd.getString("orgId"))) {
                    pd.put("orgId", pd.getString("orgId"));
                } else {
                    pd.put("orgId", TokenUtil.getTokenOrgId());
                }
            }
            List<PageData> excelList = autoExcelDao.findListByCode(pd);
            if (CollectionUtil.isEmpty(excelList)){
                return new ListResult<>(CommonCode.FAIL);
            }
            PageData excel = excelList.get(0);

            if (Tools.notEmpty(pd.getString("reportTime"))) {
                switch (excel.getInteger("fillType")) {
                    case 0:
                        pd.put("reportTime", pd.getString("reportTime") + "-01");
                        break;
                    case 1:
                        if ("1".equals(pd.getString("season"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-01-01");
                        } else if ("2".equals(pd.getString("season"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-04-01");
                        } else if ("3".equals(pd.getString("season"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-07-01");
                        } else if ("4".equals(pd.getString("season"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-10-01");
                        }
                        break;
                    case 2:
                        pd.put("reportTime", pd.getString("reportTime") + "-01-01");
                        break;
                    case 3:
                        pd.put("reportTime", pd.getString("reportTime"));
                        break;
                    case 4:
                        //填报范围 上半年 全年
                        if ("1".equals(pd.getString("isYearly"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-06-01");
                        }else if("2".equals(pd.getString("isYearly"))){
                            pd.put("reportTime", pd.getString("reportTime") + "-12-01");
                        }
                        break;
                    case 5:
                        if ("1".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-01-01");
                        }else if ("2".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-02-01");
                        }else if ("3".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-03-01");
                        }else if ("4".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-04-01");
                        }else if ("5".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-05-01");
                        }else if ("6".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-06-01");
                        }else if ("7".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-07-01");
                        }else if ("8".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-08-01");
                        }else if ("9".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-09-01");
                        }else if ("10".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-10-01");
                        }else if ("11".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-11-01");
                        }else if ("12".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-12-01");
                        }
                        break;
                    default:
                        break;
                }
            }
            pd.put("excelIds", excelList.stream().map(excelP -> excelP.getString("id")).collect(Collectors.toList()));
            if (Tools.notEmpty(excel.getString("tableName"))) {
                pd.put("tableName", "excel_bill_" + excel.getString("tableName"));
                Page page = new Page(true, pd);
                String reportTime=pd.getString("reportTime");
                if(StringUtil.isNotBlank(reportTime)){
                    String[] split= reportTime.split("-");
                    if(split.length==1){
                        pd.put("year",reportTime);
                        pd.remove("reportTime");
                    }
                }
                List<PageData> list = autoExcelDao.listPage(page);
                ListResult<PageData> Result = new ListResult<>(CommonCode.SUCCESS);
                Result.setList(list);
                Result.setTotal(page.getTotal());
                return Result;
            } else {
                ListResult<PageData> Result = new ListResult<>(CommonCode.FAIL);
                Result.setMessage("未配置表名，请联系系统管理员配置！");
                return Result;
            }

        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            return new ListResult<>(CommonCode.ERROR);
        }
    }

    /**
     * 根据code查询自定义excel
     */
    public ObjResult findByCode(PageData pd) {
        ObjResult objResult;
        try {
            PageData excel = autoExcelDao.findByCode(pd);
            excel.put("excelId", excel.getString("id"));
            List<PageData> formList = autoExcelDao.findByExcelId(excel);
            for (PageData form : formList) {
                List<PageData> colList = autoExcelDao.findByFormId(form);
                form.put("colList", colList);
            }
            excel.put("formList", formList);
            if (StringUtil.isNotBlank(excel.getString("jsonText"))){
                excel.put("jsonText", JSON.toJSONString(JSONArray.parse(excel.getString("jsonText"))));
            }
            objResult = new ObjResult(CommonCode.SUCCESS);
            objResult.setPageData(excel);
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            objResult = new ObjResult(CommonCode.ERROR);
            return objResult;
        }
        return objResult;
    }

    public ListResult<PageData> getLastvaluelist(PageData pd) {
        try {
            pd.put("getJson",1);
            PageData excel = autoExcelDao.findByCode(pd);
            pd.put("billName", "excel_bill_" + excel.getString("tableName"));
            pd.put("valueName", "excel_value_" + excel.getString("tableName"));

            PageData form = autoExcelDao.findFormById(pd);
            List<PageData> valuelist = new ArrayList<>();


            switch (excel.getInteger("fillType")) {
                case 0:
                    pd.put("reportTime", pd.getString("reportTime") + "-01");
                    break;
                case 1:
                    if ("1".equals(pd.getString("season"))) {
                        pd.put("reportTime", pd.getString("reportTime") + "-01-01");
                    } else if ("2".equals(pd.getString("season"))) {
                        pd.put("reportTime", pd.getString("reportTime") + "-04-01");
                    } else if ("3".equals(pd.getString("season"))) {
                        pd.put("reportTime", pd.getString("reportTime") + "-07-01");
                    } else if ("4".equals(pd.getString("season"))) {
                        pd.put("reportTime", pd.getString("reportTime") + "-10-01");
                    }
                    break;
                case 2:
                    pd.put("reportTime", pd.getString("reportTime") + "-01-01");
                    break;
                case 3:
                    pd.put("reportTime", pd.getString("reportTime"));
                    break;
                case 4:
                    //填报范围 上半年 全年
                    if ("1".equals(pd.getString("isYearly"))) {
                        pd.put("reportTime", pd.getString("reportTime") + "-06-01");
                    }else if("2".equals(pd.getString("isYearly"))){
                        pd.put("reportTime", pd.getString("reportTime") + "-12-01");
                    }
                    break;
                case 5:
                    if ("1".equals(pd.getString("months"))) {
                        pd.put("reportTime", pd.getString("reportTime") + "-01-01");
                    }else if ("2".equals(pd.getString("months"))) {
                        pd.put("reportTime", pd.getString("reportTime") + "-02-01");
                    }else if ("3".equals(pd.getString("months"))) {
                        pd.put("reportTime", pd.getString("reportTime") + "-03-01");
                    }else if ("4".equals(pd.getString("months"))) {
                        pd.put("reportTime", pd.getString("reportTime") + "-04-01");
                    }else if ("5".equals(pd.getString("months"))) {
                        pd.put("reportTime", pd.getString("reportTime") + "-05-01");
                    }else if ("6".equals(pd.getString("months"))) {
                        pd.put("reportTime", pd.getString("reportTime") + "-06-01");
                    }else if ("7".equals(pd.getString("months"))) {
                        pd.put("reportTime", pd.getString("reportTime") + "-07-01");
                    }else if ("8".equals(pd.getString("months"))) {
                        pd.put("reportTime", pd.getString("reportTime") + "-08-01");
                    }else if ("9".equals(pd.getString("months"))) {
                        pd.put("reportTime", pd.getString("reportTime") + "-09-01");
                    }else if ("10".equals(pd.getString("months"))) {
                        pd.put("reportTime", pd.getString("reportTime") + "-10-01");
                    }else if ("11".equals(pd.getString("months"))) {
                        pd.put("reportTime", pd.getString("reportTime") + "-11-01");
                    }else if ("12".equals(pd.getString("months"))) {
                        pd.put("reportTime", pd.getString("reportTime") + "-12-01");
                    }
                    break;
                default:
                    break;
            };
            List<PageData> colist = autoExcelDao.findcolumn(form);
            String value = "";
            for (int i = 0; i < colist.size(); i++) {
                PageData p = colist.get(i);
                String v = p.getString("columnId");
                if ("".equals(value)) {
                    value += " max(CASE c.column_Id WHEN '" + v + "' THEN v.`values` ELSE '' END) AS " + "c" + i;
                } else {
                    value += " ,max(CASE c.column_Id WHEN '" + v + "' THEN v.`values` ELSE '' END) AS " + "c" + i;
                }
            }
            pd.put("vaues", value);
            valuelist = autoExcelDao.floatvalue(pd);
            ListResult<PageData> Result = new ListResult<>(CommonCode.SUCCESS);
            Result.setList(valuelist);
            return Result;
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            return new ListResult<>(CommonCode.ERROR);
        }
    }
    /**
     * 查询值表
     */
    public ListResult<PageData> valuelist(PageData pd) {
        try {
            pd.put("getJson",1);
            List<PageData> excelList = autoExcelDao.findListByCode(pd);
            if (CollectionUtil.isEmpty(excelList)){
                return new ListResult<>(CommonCode.ERROR);
            }
            PageData excel = excelList.get(0);
            pd.put("billName", "excel_bill_" + excel.getString("tableName"));
            pd.put("valueName", "excel_value_" + excel.getString("tableName"));

            PageData form = autoExcelDao.findFormById(pd);

            List<String> orgIds = (List<String>) pd.get("orgIds");
            List<PageData> valuelist = new ArrayList<>();
            if (Tools.isEmpty(pd.getString("isSummary"))) {
                valuelist = alist(pd);
            } else {
                if ("0".equals(form.getString("isAdd"))) {
                    ListResult<PageData> Result = new ListResult<>(CommonCode.FAIL);
                    Result.setMessage("该表不进行汇总");
                    return Result;
                } else {
                    switch (excel.getInteger("fillType")) {
                        case 0:
                            pd.put("reportTime", pd.getString("reportTime") + "-01");
                            break;
                        case 1:
                            if ("1".equals(pd.getString("season"))) {
                                pd.put("reportTime", pd.getString("reportTime") + "-01-01");
                            } else if ("2".equals(pd.getString("season"))) {
                                pd.put("reportTime", pd.getString("reportTime") + "-04-01");
                            } else if ("3".equals(pd.getString("season"))) {
                                pd.put("reportTime", pd.getString("reportTime") + "-07-01");
                            } else if ("4".equals(pd.getString("season"))) {
                                pd.put("reportTime", pd.getString("reportTime") + "-10-01");
                            }
                            break;
                        case 2:
                            pd.put("reportTime", pd.getString("reportTime") + "-01-01");
                            break;
                        case 3:
                            pd.put("reportTime", pd.getString("reportTime"));
                            break;
                        case 4:
                            //填报范围 上半年 全年
                            if ("1".equals(pd.getString("isYearly"))) {
                                pd.put("reportTime", pd.getString("reportTime") + "-06-01");
                            }else if("2".equals(pd.getString("isYearly"))){
                                pd.put("reportTime", pd.getString("reportTime") + "-12-01");
                            }
                            break;
                        case 5:
                            if ("1".equals(pd.getString("months"))) {
                                pd.put("reportTime", pd.getString("reportTime") + "-01-01");
                            }else if ("2".equals(pd.getString("months"))) {
                                pd.put("reportTime", pd.getString("reportTime") + "-02-01");
                            }else if ("3".equals(pd.getString("months"))) {
                                pd.put("reportTime", pd.getString("reportTime") + "-03-01");
                            }else if ("4".equals(pd.getString("months"))) {
                                pd.put("reportTime", pd.getString("reportTime") + "-04-01");
                            }else if ("5".equals(pd.getString("months"))) {
                                pd.put("reportTime", pd.getString("reportTime") + "-05-01");
                            }else if ("6".equals(pd.getString("months"))) {
                                pd.put("reportTime", pd.getString("reportTime") + "-06-01");
                            }else if ("7".equals(pd.getString("months"))) {
                                pd.put("reportTime", pd.getString("reportTime") + "-07-01");
                            }else if ("8".equals(pd.getString("months"))) {
                                pd.put("reportTime", pd.getString("reportTime") + "-08-01");
                            }else if ("9".equals(pd.getString("months"))) {
                                pd.put("reportTime", pd.getString("reportTime") + "-09-01");
                            }else if ("10".equals(pd.getString("months"))) {
                                pd.put("reportTime", pd.getString("reportTime") + "-10-01");
                            }else if ("11".equals(pd.getString("months"))) {
                                pd.put("reportTime", pd.getString("reportTime") + "-11-01");
                            }else if ("12".equals(pd.getString("months"))) {
                                pd.put("reportTime", pd.getString("reportTime") + "-12-01");
                            }
                            break;
                        default:
                            break;
                    }
                    ;
                    if ("0".equals(form.getString("isChange"))) {
                        valuelist = addlist(pd);
                    } else {
                        Gson gson = new Gson();
                        List<PageData> jsonTextList = gson.fromJson(excel.getString("jsonText"),ArrayList.class);
                        Map<String,Object> jsonText = jsonTextList.get(form.getInteger("orderNumber")-1);
                        List<List<String>> formList = (List<List<String>>)jsonText.get("tableData");
                        List<List<String>> footList = formList.subList(formList.size()-form.getInteger("footNumber"),formList.size());
                        List<List<String>> headList = formList.subList(0,form.getInteger("headNumber"));
                        for (int i = 0; i < 3; i++) {
                            pd.put("lineNumber", i);
                            List<PageData> tablelist = addoverlist(pd);
                            List<PageData> tablelist1 = new ArrayList<>();
                            int j = 1;
                            if (i == 1 && tablelist != null && tablelist.size() > 0) {
                                for (String orgId : orgIds) {
                                    List<PageData> plist = new ArrayList<>();
                                    for (PageData p : tablelist) {
                                        if (p.getString("orgId").equals(orgId)) {
                                            plist.add(p);
                                        }
                                    }
                                    if (plist != null && plist.size() > 0){
                                        tablelist1.addAll(plist.subList(0, (plist.size() - Integer.valueOf(form.getString("footNumber")))));
                                    }
                                }
                                tablelist = tablelist1;
                            } else if (i == 2) {
//                                反转倒序的表底部
                                Collections.reverse(tablelist);
                                for (PageData pageData:tablelist){
                                    for (int n = 0;n<pageData.size();n++){
                                        pageData.put("c"+n,footList.get(tablelist.indexOf(pageData)).get(n));
                                    }
                                }

                            }else if (i == 0) {
                                for (PageData pageData:tablelist){
                                    for (int n = 0;n<pageData.size()-1;n++){
                                        pageData.put("c"+n,headList.get(tablelist.indexOf(pageData)).get(n));
                                    }
                                }

                            }
                            for (PageData p : tablelist) {
                                if ("1".equals(form.getString("isOrder")) && i == 1) {
                                    p.put("c0", j);
                                    j++;
                                }
                                valuelist.add(p);
                            }
                        }
                    }
                }
            }

            ListResult<PageData> Result = new ListResult<>(CommonCode.SUCCESS);
            Result.setList(valuelist);
            return Result;
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            return new ListResult<>(CommonCode.ERROR);
        }
    }

    /**
     * 封装list方法
     *
     * @return
     */
    public List<PageData> alist(PageData pd) throws Exception {
        PageData pd_form = new PageData();
        pd_form.put("formId", pd.getString("formId"));
        List<PageData> colist = autoExcelDao.findcolumn(pd_form);
        String value = "";
        for (int i = 0; i < colist.size(); i++) {
            PageData p = colist.get(i);
            String v = p.getString("columnId");
            if ("".equals(value)) {
                value += " max(CASE c.column_Id WHEN '" + v + "' THEN v.`values` ELSE '' END) AS " + "c" + i;
            } else {
                value += " ,max(CASE c.column_Id WHEN '" + v + "' THEN v.`values` ELSE '' END) AS " + "c" + i;
            }
        }
        pd.put("vaues", value);
        List<PageData> valuelist = autoExcelDao.findvalue(pd);
        return valuelist;
    }

    public ListResult<SafeProdExcelBean> aqTotalList(PageData pd) throws Exception {
        try {
            pd.put("getJson",1);
            List<PageData> excelList = autoExcelDao.findListByCode(pd);
            if (CollectionUtil.isEmpty(excelList)){
                return new ListResult<>(CommonCode.ERROR);
            }
            PageData excel = excelList.get(0);
            pd.put("billName", "excel_bill_" + excel.getString("tableName"));
            pd.put("valueName", "excel_value_" + excel.getString("tableName"));

            PageData pd_form = new PageData();
            pd_form.put("formId", pd.getString("formId"));
            List<PageData> colist = autoExcelDao.findcolumn(pd_form);
            String value = "";
            for (int i = 0; i < colist.size(); i++) {
                PageData p = colist.get(i);
                String v = p.getString("columnId");
                if ("".equals(value)) {
                    value += " max(CASE c.column_Id WHEN '" + v + "' THEN v.`values` ELSE '' END) AS " + "c" + i;
                } else {
                    value += " ,max(CASE c.column_Id WHEN '" + v + "' THEN v.`values` ELSE '' END) AS " + "c" + i;
                }
            }
            pd.put("vaues", value);
            List<SafeProdExcelBean> valuelist = autoExcelDao.findAqTotalValue(pd);
            List<SafeProdExcelBean> list = processPageDataList(valuelist);
            ListResult<SafeProdExcelBean> Result = new ListResult<>(CommonCode.SUCCESS);
            Result.setList(list);
            return Result;
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            return new ListResult<>(CommonCode.ERROR);
        }
    }

    public static List<SafeProdExcelBean> processPageDataList(List<SafeProdExcelBean> pageDataList) {
        Map<String, SafeProdExcelBean> ledgerMap = new HashMap<>();
        Map<String, SafeProdExcelBean> orgMap = new HashMap<>();
        Map<String, SafeProdExcelBean> numMap = new HashMap<>();
        Map<String, List<String>> orgFieldsMap = new HashMap<>(); // 用于暂存c2, c7, c10的合并值
        Map<String, Integer> orgOrderNumMap = new HashMap<>(); // 用于存储单位的当前序号
        List<SafeProdExcelBean> businessList = new ArrayList<>();

        for (SafeProdExcelBean pageData : pageDataList) {
            String c0 = pageData.getC0();

            if ("安全生产大排查大整治隐患排查情况统计台账".equals(c0)) {
                if (ledgerMap.isEmpty()) {
                    ledgerMap.put(c0, pageData);
                }
            } else if ("填报单位（科室）：".equals(c0)) {
                if (orgMap.isEmpty()) {
                    orgMap.put(c0, pageData);
                }
                String key = "填报单位（科室）合并键"; // 由于我们只保留一个合并后的PageData，所以使用固定键
                List<String> c2List = orgFieldsMap.getOrDefault(key + "c2", new ArrayList<>());
                List<String> c7List = orgFieldsMap.getOrDefault(key + "c7", new ArrayList<>());
                List<String> c10List = orgFieldsMap.getOrDefault(key + "c10", new ArrayList<>());

                c2List.add(pageData.getC2());
                c7List.add(pageData.getC7());
                c10List.add(pageData.getC10());

                orgFieldsMap.put(key + "c2", c2List);
                orgFieldsMap.put(key + "c7", c7List);
                orgFieldsMap.put(key + "c10", c10List);
            } else if ("序号".equals(c0)) {
                if (numMap.isEmpty()) {
                    numMap.put(c0, pageData);
                }
            } else {
                // 序号重新赋值
                String orgName = pageData.getC1();
                if(orgOrderNumMap.containsKey(orgName)){
                    Integer now = orgOrderNumMap.get(orgName);
                    Integer next = now + 1;
                    pageData.setC0(String.valueOf(next));
                    orgOrderNumMap.put(orgName,next);
                }else {
                    pageData.setC0("1");
                    orgOrderNumMap.put(orgName,1);
                }
                businessList.add(pageData);
            }
        }
        // 处理合并后的“序号”PageData
        if (!numMap.isEmpty()) {
            SafeProdExcelBean numPageData = numMap.values().iterator().next();
            businessList.add(0, numPageData); // 将其添加到业务数据列表的最前面
        }

        // 处理合并后的“填报单位（科室）:”PageData
        if (!orgMap.isEmpty()) {
            SafeProdExcelBean orgPageData = orgMap.values().iterator().next();
            orgPageData.setC0("填报单位（科室）:");
            orgPageData.setC2(String.join(",", orgFieldsMap.get("填报单位（科室）合并键c2")));
            orgPageData.setC7(String.join(",", orgFieldsMap.get("填报单位（科室）合并键c7")));
            orgPageData.setC10(String.join(",", orgFieldsMap.get("填报单位（科室）合并键c10")));
//            orgPageData.setC2(orgFieldsMap.get("填报单位（科室）合并键c2").stream()
//                    .filter(s -> s != null && !s.trim().isEmpty()) // 过滤null和空字符串
//                    .collect(Collectors.joining(",")));
//            orgPageData.setC7(orgFieldsMap.get("填报单位（科室）合并键c7").stream()
//                    .filter(s -> s != null && !s.trim().isEmpty()) // 过滤null和空字符串
//                    .collect(Collectors.joining(",")));
//            orgPageData.setC10(orgFieldsMap.get("填报单位（科室）合并键c10").stream()
//                    .filter(s -> s != null && !s.trim().isEmpty()) // 过滤null和空字符串
//                    .collect(Collectors.joining(",")));

            businessList.add(0, orgPageData); // 将其添加到业务数据列表的第二个位置（序号之后）
        }

        // 处理“安全生产大排查大整治隐患排查情况统计台账”PageData
        if (!ledgerMap.isEmpty()) {
            SafeProdExcelBean ledgerPageData = ledgerMap.values().iterator().next();
            businessList.add(0, ledgerPageData); // 将其添加到业务数据列表的最前面
        }

        // 对业务数据进行排序，前三行不动，从第四行开始
        int startIndex = 3;
        businessList = Stream.concat(
                businessList.stream().limit(startIndex),
                businessList.stream().skip(startIndex)
                        .sorted(Comparator.comparing(SafeProdExcelBean::getC1)
                                .thenComparing(a -> Integer.valueOf(a.getC0())))
        ).collect(Collectors.toList());

        return businessList;
    }

    /**
     * 封装addlist方法
     *
     * @return
     */
    public List<PageData> addlist(PageData pd) throws Exception {
        PageData pd_column = new PageData();
        pd_column.put("formId", pd.getString("formId"));
        List<PageData> colist = autoExcelDao.findcolumn(pd_column);
        String value = "";
        for (int i = 0; i < colist.size(); i++) {
            PageData p = colist.get(i);
            String v = p.getString("columnId");
            int isNumValue = new Integer(p.getString("isNumValue"));
            if (isNumValue == 1) {
                if (Tools.isEmpty(value)) {
                    value += "sum(CASE c.column_id WHEN '" + v + "' THEN v.real_values ELSE '' END) AS " + "c" + i;
                } else {
                    value += " ,sum(CASE c.column_id WHEN '" + v + "' THEN v.real_values ELSE '' END) AS " + "c" + i;
                }

            } else {
                if (Tools.isEmpty(value)) {
                    value += " max(CASE c.column_id WHEN '" + v + "' THEN v.real_values ELSE '' END) AS " + "c" + i;
                } else {
                    value += " ,max(CASE c.column_id WHEN '" + v + "' THEN v.real_values ELSE '' END) AS " + "c" + i;
                }

            }
        }
        pd.put("vaues", value);
        List<PageData> valuelist = autoExcelDao.addvalue(pd);
        return valuelist;
    }

    /**
     * 拼接可变行查询语句
     *
     * @return
     */
    public List<PageData> addoverlist(PageData pd) throws Exception {
        PageData pd_column = new PageData();
        pd_column.put("formId", pd.getString("formId"));
        List<PageData> colist = autoExcelDao.findcolumn(pd_column);
        PageData form = autoExcelDao.findFormById(pd_column);
        int headNumber = form.getInteger("headNumber");
        int footNumber = form.getInteger("footNumber");
//        外层select查询字段语句
        String selectval = "a.*";
        String addval = "";
//        内层select查询字段语句
        String value = "";
//        内层的筛选条件
        String whereval1 = "";
//        外层筛选条件
        String whereval2 = "";
//        内层group by 语句
        String groupval = "";
        for (int i = 0; i < colist.size(); i++) {
            PageData p = colist.get(i);
            String v = p.getString("columnId");
            int isNumValue = new Integer(p.getString("isNumValue"));
            if ("".equals(value)) {
                if ("2".equals(pd.getString("lineNumber"))) {
                    if (isNumValue == 1) {
                        value += " sum(CASE c.column_id WHEN '" + v + "' THEN v.real_values ELSE '' END) AS " + "c" + i;
                    } else {
                        value += " max(CASE c.column_id WHEN '" + v + "' THEN v.real_values ELSE '' END) AS " + "c" + i;
                    }
                } else {
                    value += " max(CASE c.column_id WHEN '" + v + "' THEN v.`values` ELSE '' END) AS " + "c" + i;
                }
            } else {
                if ("2".equals(pd.getString("lineNumber"))) {
                    if (isNumValue == 1) {
                        value += " ,sum(CASE c.column_id WHEN '" + v + "' THEN v.real_values ELSE '' END) AS " + "c" + i;
                    } else {
                        value += ", max(CASE c.column_id WHEN '" + v + "' THEN v.real_values ELSE '' END) AS " + "c" + i;
                    }
                } else {
                    value += " ,max(CASE c.column_id WHEN '" + v + "' THEN v.`values` ELSE '' END) AS " + "c" + i;
                }
            }
            switch (isNumValue) {
                case 0:
                    if ("".equals(addval)) {
                        addval += "a.c" + i;
                    } else {
                        addval += ",a.c" + i;
                    }
                    break;
                case 1:
                    addval += ",ifnull( sum( a.c" + i + " ),0) AS c" + i;
                    break;
                case 2:
                    break;
                case 4:
                    break;
                default:
                    break;
            }
        }
        if ("0".equals(pd.getString("lineNumber"))) {
            whereval1 = " v.orderNumber <= " + headNumber;
        } else if ("1".equals(pd.getString("lineNumber"))) {
            selectval+=",a.orgId ";
            whereval1 = " v.orderNumber>" + headNumber;
            whereval2 = " group by a.orderNumber,a.link_id order by a.link_id,a.orderNumber";
            groupval = ",r.org_Id,v.link_id";
        } else {
            selectval = addval;
            whereval1 = " v.orderNumber > " + headNumber;
            whereval2 = " and a.c0 !=''group by a.orderNumber order by a.orderNumber desc limit " + footNumber;
        }
        pd.put("selectval", selectval);
        pd.put("vaues", value);
        pd.put("whereval1", whereval1);
        pd.put("whereval2", whereval2);
        pd.put("groupval", groupval);
        pd.put("orgCode", pd.getString("orgId"));
        List<PageData> valuelist;
        valuelist = autoExcelDao.overvalue(pd);
        return valuelist;
    }

    /**
     * 删除
     *
     * @throws Exception
     */
    public ObjResult delete(PageData pd) {
        ObjResult objResult;
        try {
            List<PageData> excelList = autoExcelDao.findListByCode(pd);
            if (CollectionUtil.isEmpty(excelList)){
                return new ObjResult(CommonCode.ERROR);
            }
            PageData excel = excelList.get(0);
            pd.put("tableName", "excel_bill_" + excel.getString("tableName"));
            pd.put("valueName","excel_value_" + excel.getString("tableName"));
            autoExcelDao.delete(pd);
            List<PageData> linkList = autoExcelDao.findLinksById(pd);
            autoExcelDao.delLinkByBillId(pd);
            if (linkList.size()>0){
                pd.put("list",linkList);
                autoExcelDao.delValue(pd);
            }
            formWorkflowService.deleteProcessInstance(pd.getString("id"));
            objResult = new ObjResult(CommonCode.SUCCESS);
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            objResult = new ObjResult(CommonCode.ERROR);
            return objResult;
        }
        return objResult;
    }

    /**
     * 提交单据
     *
     * @param formId 单据id
     */
    public HashMap<String, Object> submit(String formId, String code) throws Exception {
        // 安全隐患排查附件校验
        if("0004".equals(code)) {
            PageData pd = new PageData();
            pd.put("parentId",formId);
            ObjResult objResult = fileService.listParentId(pd);
            if(null == objResult || null == objResult.getPageData()){
                throw new Exception("附件未上传");
            }
            List<PageData> list_doc = (List<PageData>) objResult.getPageData().get("list_doc");
            if(CollectionUtil.isEmpty(list_doc)){
                throw new Exception("附件未上传");
            }
        }
        PageData pageData = new PageData();
        pageData.put("status", String.valueOf(FormStatusConstant.FLOW));
        ArrayList<String> ids = new ArrayList<>();
        ids.add(formId);
        pageData.put("id", ids);
        pageData.put("code", code);
        List<PageData> excelList = autoExcelDao.findListByCode(pageData);
        if (CollectionUtil.isEmpty(excelList)){
            return new HashMap<>();
        }
        PageData excel = excelList.get(0);

        pageData.put("tableName", "excel_bill_" + excel.getString("tableName"));
        autoExcelDao.refer(pageData);

        HashMap<String, Object> hs = new HashMap<>();
        hs.put("code", code);
        //将需要提交的流程变量返回，用于工作流侧
        return hs;
    }

    /**
     * 退回单据
     *
     * @param formId 单据id
     */
    public Boolean reject(String formId, int formState, String comment){
        try {
            ProcessInstance processInstance = runtimeService.createProcessInstanceQuery().processInstanceBusinessKey(formId).includeProcessVariables().singleResult();
            String code = (String) processInstance.getProcessVariables().get("code");
            PageData param = new PageData();
            param.put("code",code);
            List<PageData> excelList = autoExcelDao.findListByCode(param);
            if (CollectionUtil.isEmpty(excelList)){
                throw new MyRuntimeException("未找到excel配置");
            }
            PageData excel = excelList.get(0);
            String tableName = excel.getString("tableName");
            PageData pageData = new PageData();
            pageData.put("status", String.valueOf(formState));
            pageData.put("id", Collections.singletonList(formId));
            pageData.put("tableName", "excel_bill_" + tableName);
            if (AutoExcelConstant.JZB_CODE.equals(code)){
                ExcelBill excelBill = autoExcelMapper.findById("excel_bill_" + tableName, formId);
                if (excelBill != null && StringUtil.isNotBlank(excelBill.getPayrollSettlementId())){
                    ApprovalMessage approvalMessage = new ApprovalMessage();
                    approvalMessage.setDataSourceType(YthConstant.DATA_NINGBO);
                    approvalMessage.setFilingUserName(UserCache.userIdToName(TokenUtil.getTokenUserId()));
                    approvalMessage.setFormType(SYS_AUTO_EXCEL);
                    approvalMessage.setDataSourceId(excelBill.getPayrollSettlementId());
                    approvalMessage.setOpinion(comment);
                    approvalMessage.setIsApprove(1);
                    approvalMessage.setIsSend(1);
                    approvalMessageService.save(approvalMessage);
                }
            }
            autoExcelDao.refer(pageData);
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            throw new MyRuntimeException("回退失败");
        }
        return true;
    }

    /**
     * 自定义表格 用submit方法
     * @param id
     * @return
     */
    @Override
    public HashMap<String, Object> commit(String id) {
        return null;
    }

    public Boolean finish(String formId) {
        try {
            PageData pageData = new PageData();
            pageData.put("status", String.valueOf(FormStatusConstant.FINISH));
            pageData.put("id", Collections.singletonList(formId));
//            流程结束获取流程变量
            HistoricProcessInstance historicProcessInstance = historyService.createHistoricProcessInstanceQuery().includeProcessVariables().processInstanceBusinessKey(formId).singleResult();
            String code = historicProcessInstance.getProcessVariables().get("code").toString();
            PageData pd = new PageData();
            pd.put("code", code);
            List<PageData> excelList = autoExcelDao.findListByCode(pd);
            if (CollectionUtil.isEmpty(excelList)){
                throw new MyRuntimeException("未找到excel配置");
            }
            PageData excelpd = excelList.get(0);
            String tableName =  "excel_bill_" + excelpd.getString("tableName");
            pageData.put("tableName", tableName);
            if (AutoExcelConstant.JZB_CODE.equals(code)){
                ExcelBill excelBill = autoExcelMapper.findById(tableName, formId);
                if (excelBill != null && StringUtil.isNotBlank(excelBill.getPayrollSettlementId())){
                    ApprovalMessage approvalMessage = new ApprovalMessage();
                    approvalMessage.setDataSourceType(YthConstant.DATA_NINGBO);
                    approvalMessage.setFilingUserName(UserCache.userIdToName(TokenUtil.getTokenUserId()));
                    approvalMessage.setFormType(SYS_AUTO_EXCEL);
                    approvalMessage.setDataSourceId(excelBill.getPayrollSettlementId());
                    approvalMessage.setOpinion("同意");
                    approvalMessage.setIsApprove(2);
                    approvalMessage.setIsSend(1);
                    approvalMessageService.save(approvalMessage);
                }
            }
            autoExcelDao.refer(pageData);
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            throw new MyRuntimeException(new ObjResult("单据流转完成时修改单据状态失败！"));
        }
        return true;
    }

    /**
     * @param formIds 单据ids
     */
    public List<PageData> findByIds(List<String> formIds) {
        try {
            PageData pageData = new PageData();
            pageData.put("ids", formIds);
            List<PageData> valueList = new ArrayList<>();
            for (String formId : formIds) {
//                流程未结束时获取流程变量
                ProcessInstance process = runtimeService.createProcessInstanceQuery().processInstanceBusinessKey(formId).includeProcessVariables().singleResult();
                String code = process.getProcessVariables().get("code").toString();
                PageData pd = new PageData();
                pd.put("code", code);
                List<PageData> excelList = autoExcelDao.findListByCode(pd);
                if (CollectionUtil.isEmpty(excelList)){
                    throw new MyRuntimeException("未找到excel配置");
                }
                PageData excelpd = excelList.get(0);
                pageData.put("tableName", "excel_bill_" + excelpd.getString("tableName"));
                List<PageData> billList = autoExcelDao.findByIds(pageData);
                for (PageData bill : billList) {
                    bill.put("year", bill.getString("reportTime"));
                    bill.put("UNITNAME", bill.getString("orgName"));
                    bill.put("table", bill.getString("table"));
                }
                valueList.addAll(billList);
            }
            return valueList;
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            throw new MyRuntimeException(new ObjResult("根据单据id列表查询报表失败！"));
        }
    }

    @Override
    public List<Integer> getFormType() {
        return Arrays.asList(SYS_AUTO_EXCEL);
    }

    /**
     * @param formIds 单据ids
     */
    public List<PageData> findHistoryByIds(List<String> formIds, String code) {
        try {
            PageData pageData = new PageData();
            pageData.put("ids", formIds);
            List<PageData> valueList = new ArrayList<>();
            for (String formId : formIds) {
//                流程未结束时获取流程变量
                if (StringUtil.isBlank(code)){
                    HistoricProcessInstance historicProcessInstance = historyService.createHistoricProcessInstanceQuery().includeProcessVariables().processInstanceBusinessKey(formId).singleResult();
                    code = historicProcessInstance.getProcessVariables().get("code").toString();
                }
                PageData pd = new PageData();
                pd.put("code", code);
                List<PageData> excelList = autoExcelDao.findListByCode(pd);
                if (CollectionUtil.isEmpty(excelList)){
                    throw new MyRuntimeException("未找到excel配置");
                }
                PageData excelpd = excelList.get(0);
                pageData.put("tableName", "excel_bill_" + excelpd.getString("tableName"));
                List<PageData> billList = autoExcelDao.findByIds(pageData);
                for (PageData bill : billList) {
                    bill.put("year", bill.getString("reportTime"));
                    bill.put("UNITNAME", bill.getString("orgName"));
                    bill.put("table", bill.getString("table"));
                }
                valueList.addAll(billList);
            }
            return valueList;
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            throw new MyRuntimeException(new ObjResult("根据单据id列表查询报表失败！"));
        }
    }
    public void goBackToStash(String formId) {
        try {
            PageData pageData = new PageData();
            HistoricProcessInstance historicProcessInstance = historyService.createHistoricProcessInstanceQuery().includeProcessVariables().processInstanceBusinessKey(formId).singleResult();
            String code = historicProcessInstance.getProcessVariables().get("code").toString();
            PageData pd = new PageData();
            pd.put("code", code);
            List<PageData> excelList = autoExcelDao.findListByCode(pd);
            if (CollectionUtil.isEmpty(excelList)){
                throw new MyRuntimeException("未找到excel配置");
            }
            PageData excelpd = excelList.get(0);
            pageData.put("tableName", "excel_bill_" + excelpd.getString("tableName"));

            pageData.put("status", String.valueOf(FormStatusConstant.UNREVIEWED));
            pageData.put("id", Collections.singletonList(formId));
            autoExcelDao.refer(pageData);
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
        }
    }

    /**
     * 导入报表
     *
     * @param
     * @throws Exception
     */
    public ObjResult uploadFile(MultipartHttpServletRequest request) {
        ObjResult objResult;
        try {
//            按表格的标签名称导入
            PageData pd = new PageData(request);
            PageData p = new PageData();
            MultipartFile mp = request.getFile("file");
            if (mp != null) {
                InputStream stream = mp.getInputStream();
                //接收文件流
                //将流给工作簿
                Workbook book = null;
                try {
                    book = new HSSFWorkbook(stream);
                } catch (Exception ex) {
                    // 解决read error异常
                    stream = mp.getInputStream();
                    book = new XSSFWorkbook(stream);
                }
                //遍历sheet
                for (int i = 0; i < book.getNumberOfSheets(); i++) {
                    List<PageData> list = new ArrayList<>();
                    Sheet sheet = book.getSheetAt(i);
                    String sheetname = sheet.getSheetName();
//                    PageData orgpd = autoExcelDao.findFormby(sheetname);
                    //遍历行
                    for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                        String check = "";
                        Row row = sheet.getRow(rowNum);
                        PageData pd_row = new PageData();
                        if (null == row) {
                            continue;
                        }
                        //遍历列
                        for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {
                            Cell cell = row.getCell(cellNum);
                            if (null == cell) {
                                continue;
                            }

//                            if(orgpd==null){
                            pd_row.put("c" + cellNum, getvalue(cell));
//                            }else {
//                                pd_row.put("c" + cellNum, getvalue(cell,orgpd.getString("isChange")));
//                            }

                        }
                        list.add(pd_row);
                    }

                    pd.put(sheet.getSheetName(), list);
                }
                book.close();
                stream.close();
            }
            objResult = new ObjResult(CommonCode.SUCCESS);
            objResult.setPageData(pd);

        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            objResult = new ObjResult(CommonCode.ERROR);
        }
        return objResult;
    }

    /**
     * 导出报表
     *
     * @param
     * @throws Exception
     */
    public void exportExcel(PageData pd, HttpServletResponse response, OutputStream os) throws Exception {
        List<HashMap> pdList = (List<HashMap>) pd.get("list");
        os = response.getOutputStream();//取得输出流
        response.reset();//清空输出流
        //下面是对中文文件名的处理
        response.setCharacterEncoding("UTF-8");//设置相应内容的编码格式
        PageData excel = autoExcelDao.findByCode(pd);

//读取系统中的模板
        InputStream inputStream;
        excel.put("parentId", excel.getString("id"));
        List<PageData> fileList = (List<PageData>) fileService.listParentId(excel).getPageData().get("list_doc");

        String separator = File.separator;
        if (fileList.size() > 0 && fileList.get(0) != null) {
            File file = new File(fileList.get(0).getString("filePath") + separator + fileList.get(0).getString("itFileName"));
            try {
                inputStream = new FileInputStream(file);
            }catch (Exception e){
                throw e;
            }

        } else {
            return;
        }


        response.setHeader("Content-Disposition", "attachment.xls");

        response.setContentType("application/msexcel");//定义输出类型

        String tableNames[] = new String[pdList.size()];
        int t = 0;
//        获取报表额orgId
        for (HashMap pdd : pdList) {
            PageData p = new PageData();
            tableNames[t] = pdd.keySet().toString().substring(1, pdd.keySet().toString().length() - 1);
            t++;
        }

//        克隆模板得到一个新的excel模板
        DynamicOperateExcelUtils dynamicOperateExcelUtils = new DynamicOperateExcelUtils(inputStream, tableNames);
//        获取工作簿
        Workbook workbook = dynamicOperateExcelUtils.getWorkbook();
//        遍历所有表，将每张表的数据塞入表中
        String message = "";
        for (int i = 0; i < pdList.size(); i++) {
//            获取表格
            Sheet sheet = dynamicOperateExcelUtils.getSheet(i);
//           获取对应的表格数据
            HashMap<String, Object> hashMap = (HashMap<String, Object>) pdList.get(i);
            PageData p = new PageData();
            for (String s : hashMap.keySet()) {
                p.put(s, hashMap.get(s));
            }
//            获得前端传过来的每行的数据存入list
            List<List<String>> valueList = (List<List<String>>) p.get("data");
//            可变行报表的插入方式
            List<List<String>> addList = (List<List<String>>) p.get("map");

//            获取当前表的所有列

            List<PageData> columnList = autoExcelDao.findcolumn(p);
            String columns[] = new String[columnList.size()];
            int j = 0;
            for (PageData col : columnList) {
                columns[j] = "c" + j + "," + col.getString("isNumValue");
                j++;
            }
//            插入数据
            try {
                for (int a = 0; a < valueList.size(); a++) {
//                可变行报表的增行
                    if (a > 0 && addList != null && addList.size() > 0 && (addList.get(a).get(0) == addList.get(a - 1).get(0))) {
                        try {
                            dynamicOperateExcelUtils.appendRows(a, valueList.get(a), columns);
                        } catch (Exception e) {
                            if (Tools.isEmpty(message)) {
                                message += autoExcelDao.findFormById(p).getString("name");
                            } else {
                                message += "," + autoExcelDao.findFormById(p).getString("name");
                            }
                            break;
                        }

                    } else {
//                    非增行的替换模板单元格的数据
                        for (int b = 0; b < columnList.size(); b++) {
                            try {
                                dynamicOperateExcelUtils.replaceCellValue(a, b, valueList.get(a).get(b));
                            } catch (Exception e) {
                                if (Tools.isEmpty(message)) {
                                    message += autoExcelDao.findFormById(p).getString("name");
                                } else {
                                    message += "," + autoExcelDao.findFormById(p).getString("name");
                                }
                                continue;
                            }
                        }

                    }

                }
            }catch (IndexOutOfBoundsException e){
                log.error(e.getMessage());
            }

//            if (p.getString("formId").equals("ta_13") || p.getString("formId").equals("ta_14")) {
//                for (int a = 0; a < valueList.size(); a++) {
//                    if (a == 5 || a == 6 || (p.getString("orgId").equals("ta_14") && a > 6 && (addList.get(a).get(0) != addList.get(a - 1).get(0)))) {
//                        for (int b = 0; b < columnList.size(); b++) {
//                            try {
//
//                                dynamicOperateExcelUtils.replaceCellValue(a, b, valueList.get(a).get(b));
//
//                            } catch (Exception e) {
//                                if (Tools.isEmpty(message)) {
//                                    message += autoExcelDao.findFormById(p).getString("name");
//                                } else {
//                                    message += "," + autoExcelDao.findFormById(p).getString("name");
//                                }
//                                continue;
//                            }
//                        }
//                    }
//                }
//            }else {
//                continue;
//            }
//            替换sheet的名称
            workbook.setSheetName(i, autoExcelDao.findFormById(p).getString("name"));
//            强制执行公式
            sheet.setForceFormulaRecalculation(true);
        }

//        dynamicOperateExcelUtils.exportExcel("D:/excel2.xlsx");
        workbook.write(os);
        os.flush();
        os.close();
        workbook.close();
    }

    //获取单元格值
    public String getvalue(Cell cell) {
        return getvalue(cell, null);
    }

    public String getvalue(Cell cell, String type) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
//        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
//            cell.setCellType(CellType.STRING);
//        }
        //判断数据的类型
        switch (cell.getCellTypeEnum()) {
            case NUMERIC: //数字
                if (isCellDateFormatted(cell)) {
                    short format = cell.getCellStyle().getDataFormat();
                    SimpleDateFormat sdf = null;
                    //					if(format == 14 || format == 31 || format == 57 || format == 58){
                    //日期
                    sdf = new SimpleDateFormat("yyyy年MM月dd日");
                    double value = cell.getNumericCellValue();
                    Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
                    cellValue = sdf.format(date);
                } else {
                    //    科学计数法解决数值转String的精度问题
                    cellValue = String.valueOf(getRealStringValueOfDouble(cell.getNumericCellValue()));
                    cell.setCellType(CellType.STRING);

                }
                break;
            case STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: //公式

                if (type != null && "0".equals(type)) {
                    try {
                        cellValue = String.valueOf(getRealStringValueOfDouble(cell.getNumericCellValue()));
                        cell.setCellType(CellType.STRING);
                    } catch (IllegalStateException e) {
                        try {
                            cellValue = String.valueOf(cell.getStringCellValue());
                        } catch (IllegalStateException el) {
                            cellValue = "=" + String.valueOf(cell.getCellFormula());

                        }

                    }
                } else {
                    cellValue = "=" + String.valueOf(cell.getCellFormula());
                }
                break;
            case BLANK: //空值
                cellValue = "";
                break;
            case ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    //    科学计数法解决数值转String的精度问题
    private static String getRealStringValueOfDouble(Double d) {
        String doubleStr = d.toString();
        boolean b = doubleStr.contains("E");
        int indexOfPoint = doubleStr.indexOf('.');
        if (b) {
            int indexOfE = doubleStr.indexOf('E');
            BigInteger xs = new BigInteger(doubleStr.substring(indexOfPoint
                    + BigInteger.ONE.intValue(), indexOfE));
            int pow = Integer.valueOf(doubleStr.substring(indexOfE
                    + BigInteger.ONE.intValue()));
            int xsLen = xs.toByteArray().length;
            int scale = xsLen - pow > 0 ? xsLen - pow : 0;
            doubleStr = String.format("%." + scale + "f", d);
        } else {
            Matcher m = PATTERN.matcher(doubleStr);
            if (m.find()) {
                doubleStr = doubleStr.replace(".0", "");
            }
        }
        return doubleStr;
    }


    public static boolean isCellDateFormatted(Cell cell) {
        if (cell == null) {
            return false;
        }
        boolean bDate = false;

        double d = cell.getNumericCellValue();
        if (isValidExcelDate(d)) {
            CellStyle style = cell.getCellStyle();
            if (style == null) {
                return false;
            }
            int i = style.getDataFormat();
            String f = style.getDataFormatString();
            bDate = isADateFormat(i, f);
        }
        return bDate;
    }

    public static boolean isADateFormat(int formatIndex, String formatString) {
        if (isInternalDateFormat(formatIndex)) {
            return true;
        }

        if ((formatString == null) || (formatString.length() == 0)) {
            return false;
        }

        String fs = formatString;
        //下面这一行是自己手动添加的 以支持汉字格式wingzing
        fs = fs.replaceAll("[\"|\']", "").replaceAll("[年|月|日|时|分|秒|毫秒|微秒]", "");

        fs = fs.replaceAll("\\\\-", "-");

        fs = fs.replaceAll("\\\\,", ",");

        fs = fs.replaceAll("\\\\.", ".");

        fs = fs.replaceAll("\\\\ ", " ");

        fs = fs.replaceAll(";@", "");

        fs = fs.replaceAll("^\\[\\$\\-.*?\\]", "");

        fs = fs.replaceAll("^\\[[a-zA-Z]+\\]", "");

        return (fs.matches("^[yYmMdDhHsS\\-/,. :]+[ampAMP/]*$"));
    }

    public static boolean isInternalDateFormat(int format) {
        switch (format) {
            case 14:
            case 15:
            case 16:
            case 17:
            case 18:
            case 19:
            case 20:
            case 21:
            case 22:
            case 45:
            case 46:
            case 47:
                return true;
            case 23:
            case 24:
            case 25:
            case 26:
            case 27:
            case 28:
            case 29:
            case 30:
            case 31:
                return true;
            case 32:
            case 33:
            case 34:
            case 35:
            case 36:
            case 37:
            case 38:
            case 39:
            case 40:
            case 41:
            case 42:
            case 43:
            case 44:
        }
        return false;
    }

    public static boolean isValidExcelDate(double value) {
        return (value > -4.940656458412465E-324D);
    }

    public ListResult<PageData> dataList(PageData pd) {
        try {

            List<PageData> excelList = autoExcelDao.findListByCode(pd);
            if (CollectionUtil.isEmpty(excelList)){
                return new ListResult<>(CommonCode.FAIL);
            }
            PageData excel = excelList.get(0);

            if (Tools.notEmpty(pd.getString("reportTime"))) {
                switch (excel.getInteger("fillType")) {
                    case 0:
                        pd.put("reportTime", pd.getString("reportTime") + "-01");
                        break;
                    case 1:
                        if ("1".equals(pd.getString("season"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-01-01");
                        } else if ("2".equals(pd.getString("season"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-04-01");
                        } else if ("3".equals(pd.getString("season"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-07-01");
                        } else if ("4".equals(pd.getString("season"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-10-01");
                        }
                        break;
                    case 2:
                        pd.put("reportTime", pd.getString("reportTime") + "-01-01");
                        break;
                    case 3:
                        pd.put("reportTime", pd.getString("reportTime"));
                        break;
                    case 4:
                        //填报范围 上半年 全年
                        if ("1".equals(pd.getString("isYearly"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-06-01");
                        }else if("2".equals(pd.getString("isYearly"))){
                            pd.put("reportTime", pd.getString("reportTime") + "-12-01");
                        }
                        break;
                    case 5:
                        if ("1".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-01-01");
                        }else if ("2".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-02-01");
                        }else if ("3".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-03-01");
                        }else if ("4".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-04-01");
                        }else if ("5".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-05-01");
                        }else if ("6".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-06-01");
                        }else if ("7".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-07-01");
                        }else if ("8".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-08-01");
                        }else if ("9".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-09-01");
                        }else if ("10".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-10-01");
                        }else if ("11".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-11-01");
                        }else if ("12".equals(pd.getString("months"))) {
                            pd.put("reportTime", pd.getString("reportTime") + "-12-01");
                        }
                        break;
                    default:
                        break;
                }
            }
            pd.put("excelIds", excelList.stream().map(excelP -> excelP.getString("id")).collect(Collectors.toList()));
            if (Tools.notEmpty(excel.getString("tableName"))) {
                pd.put("tableName", "excel_bill_" + excel.getString("tableName"));

                List<PageData> list = autoExcelDao.dataList(pd);
                ListResult<PageData> result = new ListResult<>(CommonCode.SUCCESS);
                result.setList(list);
                return result;
            } else {
                ListResult<PageData> Result = new ListResult<>(CommonCode.FAIL);
                Result.setMessage("未配置表名，请联系系统管理员配置！");
                return Result;
            }

        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            return new ListResult<>(CommonCode.ERROR);
        }
    }

    public void exportExcels(PageData pd, HttpServletResponse response, OutputStream os) throws Exception {
        PageData excelForm = autoExcelDao.findByCode(pd);
        excelForm.put("excelId", excelForm.getString("id"));
        List<PageData> formList = autoExcelDao.findByExcelId(excelForm);
        pd.put("formId",formList.get(0).getString("formId"));

        ListResult<SafeProdExcelBean> aqTotalResult = this.aqTotalList(pd);
        List<SafeProdExcelBean> aqTotalList = aqTotalResult.getList();
        List<HashMap> pdList = new ArrayList<>();
        List<List<String>> data = new ArrayList<>();
        for (SafeProdExcelBean bean : aqTotalList) {
            // 单行数据转换
            List<String> row = new ArrayList<>();
            row.add(bean.getC0());
            row.add(bean.getC1());
            row.add(bean.getC2());
            row.add(bean.getC3());
            row.add(bean.getC4());
            row.add(bean.getC5());
            row.add(bean.getC6());
            row.add(bean.getC7());
            row.add(bean.getC8());
            row.add(bean.getC9());
            row.add(bean.getC10());
            row.add(bean.getC11());
            data.add(row);
        }
        Map<String, Object> item = new HashMap<>();
        item.put("map",new ArrayList<>());
        item.put("data",data);
        item.put("formId",formList.get(0).getString("formId"));
        pdList.add((HashMap) item);

        os = response.getOutputStream();//取得输出流
        response.reset();//清空输出流
        //下面是对中文文件名的处理
        response.setCharacterEncoding("UTF-8");//设置相应内容的编码格式

//读取系统中的模板
        InputStream inputStream;
        excelForm.put("parentId", excelForm.getString("id"));
        List<PageData> fileList = (List<PageData>) fileService.listParentId(excelForm).getPageData().get("list_doc");

        String separator = File.separator;
        if (fileList.size() > 0 && fileList.get(0) != null) {
            File file = new File(fileList.get(0).getString("filePath") + separator + fileList.get(0).getString("itFileName"));
            try {
                inputStream = new FileInputStream(file);
            }catch (Exception e){
                throw e;
            }

        } else {
            return;
        }


        response.setHeader("Content-Disposition", "attachment.xls");

        response.setContentType("application/msexcel");//定义输出类型

        String tableNames[] = new String[pdList.size()];
        int t = 0;
//        获取报表额orgId
        for (HashMap pdd : pdList) {
            PageData p = new PageData();
            tableNames[t] = pdd.keySet().toString().substring(1, pdd.keySet().toString().length() - 1);
            t++;
        }

//        克隆模板得到一个新的excel模板
        DynamicOperateExcelUtils dynamicOperateExcelUtils = new DynamicOperateExcelUtils(inputStream, tableNames);
//        获取工作簿
        Workbook workbook = dynamicOperateExcelUtils.getWorkbook();
//        遍历所有表，将每张表的数据塞入表中
        String message = "";
        for (int i = 0; i < pdList.size(); i++) {
//            获取表格
            Sheet sheet = dynamicOperateExcelUtils.getSheet(i);
//           获取对应的表格数据
            HashMap<String, Object> hashMap = (HashMap<String, Object>) pdList.get(i);
            PageData p = new PageData();
            for (String s : hashMap.keySet()) {
                p.put(s, hashMap.get(s));
            }
//            获得前端传过来的每行的数据存入list
            List<List<String>> valueList = (List<List<String>>) p.get("data");
//            可变行报表的插入方式
            List<List<String>> addList = (List<List<String>>) p.get("map");

//            获取当前表的所有列

            List<PageData> columnList = autoExcelDao.findcolumn(p);
            String columns[] = new String[columnList.size()];
            int j = 0;
            for (PageData col : columnList) {
                columns[j] = "c" + j + "," + col.getString("isNumValue");
                j++;
            }
//            插入数据
            try {
                for (int a = 0; a < valueList.size(); a++) {
//                可变行报表的增行
                    if (a > 0 && addList != null && addList.size() > 0 && (addList.get(a).get(0) == addList.get(a - 1).get(0))) {
                        try {
                            dynamicOperateExcelUtils.appendRows(a, valueList.get(a), columns);
                        } catch (Exception e) {
                            if (Tools.isEmpty(message)) {
                                message += autoExcelDao.findFormById(p).getString("name");
                            } else {
                                message += "," + autoExcelDao.findFormById(p).getString("name");
                            }
                            break;
                        }

                    } else {
//                    非增行的替换模板单元格的数据
                        for (int b = 0; b < columnList.size(); b++) {
                            try {
                                dynamicOperateExcelUtils.replaceCellValue(a, b, valueList.get(a).get(b));
                            } catch (Exception e) {
                                if (Tools.isEmpty(message)) {
                                    message += autoExcelDao.findFormById(p).getString("name");
                                } else {
                                    message += "," + autoExcelDao.findFormById(p).getString("name");
                                }
                                continue;
                            }
                        }

                    }

                }
            }catch (IndexOutOfBoundsException e){
                log.error(e.getMessage());
            }

//            if (p.getString("formId").equals("ta_13") || p.getString("formId").equals("ta_14")) {
//                for (int a = 0; a < valueList.size(); a++) {
//                    if (a == 5 || a == 6 || (p.getString("orgId").equals("ta_14") && a > 6 && (addList.get(a).get(0) != addList.get(a - 1).get(0)))) {
//                        for (int b = 0; b < columnList.size(); b++) {
//                            try {
//
//                                dynamicOperateExcelUtils.replaceCellValue(a, b, valueList.get(a).get(b));
//
//                            } catch (Exception e) {
//                                if (Tools.isEmpty(message)) {
//                                    message += autoExcelDao.findFormById(p).getString("name");
//                                } else {
//                                    message += "," + autoExcelDao.findFormById(p).getString("name");
//                                }
//                                continue;
//                            }
//                        }
//                    }
//                }
//            }else {
//                continue;
//            }
//            替换sheet的名称
            workbook.setSheetName(i, autoExcelDao.findFormById(p).getString("name"));
//            强制执行公式
            sheet.setForceFormulaRecalculation(true);
        }

//        dynamicOperateExcelUtils.exportExcel("D:/excel2.xlsx");
        workbook.write(os);
        os.flush();
        os.close();
        workbook.close();
    }
}

